package org.petstore.persistence.impl;

import org.petstore.domain.Account;
import org.petstore.persistence.UserDAO;
import org.petstore.persistence.util.JDBCUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

public class UserDAOImpl implements UserDAO {
    private final JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());

    private static final String FIND_USER_BY_USERNAME_AND_PASSWORD = "SELECT SIGNON.USERNAME,ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2,ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE,PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA WHERE ACCOUNT.USERID =? AND SIGNON.PASSWORD = ? AND SIGNON.USERNAME = ACCOUNT.USERID AND PROFILE.USERID = ACCOUNT.USERID AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";
    private static final String INSERT_ACCOUNT = "INSERT INTO ACCOUNT(EMAIL, FIRSTNAME, LASTNAME, STATUS, ADDR1, ADDR2, CITY, STATE, ZIP, COUNTRY, PHONE, USERID)VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
    private static final String INSERT_PROFILE = "INSERT INTO PROFILE (LANGPREF, FAVCATEGORY, USERID,MYLISTOPT,BANNEROPT)VALUES (?, ?, ?,?,?)";
    private static final String INSERT_SIGN_ON = "INSERT INTO SIGNON (USERNAME,PASSWORD)VALUES (?, ?)";
    private static final String GET_USER_BY_USERID = "SELECT * FROM ACCOUNT WHERE USERID = ? ";
    private static final String FIND_USER_BY_USERNAME = "SELECT SIGNON.USERNAME,ACCOUNT.EMAIL,ACCOUNT.FIRSTNAME,ACCOUNT.LASTNAME,ACCOUNT.STATUS,ACCOUNT.ADDR1 AS address1,ACCOUNT.ADDR2 AS address2,ACCOUNT.CITY,ACCOUNT.STATE,ACCOUNT.ZIP,ACCOUNT.COUNTRY,ACCOUNT.PHONE,PROFILE.LANGPREF AS languagePreference,PROFILE.FAVCATEGORY AS favouriteCategoryId,PROFILE.MYLISTOPT AS listOption,PROFILE.BANNEROPT AS bannerOption,BANNERDATA.BANNERNAME FROM ACCOUNT, PROFILE, SIGNON, BANNERDATA WHERE ACCOUNT.USERID =?  AND SIGNON.USERNAME = ACCOUNT.USERID AND PROFILE.USERID = ACCOUNT.USERID AND PROFILE.FAVCATEGORY = BANNERDATA.FAVCATEGORY";
    private static final String DELETE_ACCOUNT = "DELETE FROM ACCOUNT WHERE USERID = ?";
    private static final String DELETE_PROFILE = "DELETE FROM PROFILE WHERE USERID = ?";
    private static final String DELETE_SIGN_OG = "DELETE FROM SIGNON WHERE USERNAME = ?";

    //todo 修改User这里的做法是:先删除 后添加 以后有时间再改

    /**
     * description 用于登录
     *
     * @param username:
     * @param password:
     * @return Account
     * @author huangzhangyan
     * @date 2022/1/10 15:30
     */
    @Override
    public Account findUserByUsernameAndPassword(String username, String password) {
        try {
            return template.queryForObject(FIND_USER_BY_USERNAME_AND_PASSWORD, new BeanPropertyRowMapper<>(Account.class), username, password);
        } catch (DataAccessException e) {
            return null;
        }
    }

    /**
     * description 查询单个用户的信息
     *
     * @param username:
     * @return Account
     * @author huangzhangyan
     * @date 2022/1/10 15:30
     */
    @Override
    public Account getUserInfoByUsername(String username) {
        Account user;
        try {
            user = template.queryForObject(FIND_USER_BY_USERNAME, new BeanPropertyRowMapper<>(Account.class), username);
        } catch (DataAccessException e) {
            user = null;
        }
        return user;
    }

    /**
     * description 新增用户
     *
     * @param user :
     * @author huangzhangyan
     * @date 2022/1/10 15:30
     */
    @Override
    public void addUser(Account user) {
        if (user == null) {
            return;
        }
        template.update(INSERT_ACCOUNT, user.getEmail(), user.getFirstName(), user.getLastName(), user.getStatus(), user.getAddress1(), user.getAddress2(), user.getCity(), user.getState(), user.getZip(), user.getCountry(), user.getPhone(), user.getUsername());
        template.update(INSERT_PROFILE, user.getLanguagePreference(), user.getFavouriteCategoryId(), user.getUsername(), user.isListOption(), user.isBannerOption());
        template.update(INSERT_SIGN_ON, user.getUsername(), user.getPassword());

    }

    /**
     * description 通过查询Username判断User是否存在
     *
     * @param username: 被查询的username
     * @return boolean 返回是否存在
     * @author huangzhangyan
     * @date 2021/11/1 1:10
     */
    @Override
    public boolean isUserExistByUsername(String username) {

        try {
            Account user = template.queryForObject(GET_USER_BY_USERID, new BeanPropertyRowMapper<>(Account.class), username);
            return user != null;
        } catch (DataAccessException e) {
            return false;
        }

    }

    /**
     * description 根据username删除用户 表ACCOUNT PROFILE SIGN_OG
     *
     * @param username :
     * @author huangzhangyan
     * @date 2022/1/10 15:29
     */
    @Override
    public void deleteUser(String username) {
        template.update(DELETE_ACCOUNT, username);
        template.update(DELETE_PROFILE, username);
        template.update(DELETE_SIGN_OG, username);
    }
}